package xzy.dao;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;
import xzy.pojo.Grade;
import xzy.pojo.PageBean;
import xzy.pojo.Student;
import xzy.pojo.StudentMes;
import xzy.util.StringUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Set;

@Repository("studentMesMapper")
public class StudentMesMapperImpL implements StudentMesMapper {
    @Autowired
    private SqlSessionTemplate sqlSessionTemplate;

    public ResultSet getStudentMesAll(PageBean pageBean, StudentMes studentMes, String bbirthday, String ebirthday) throws SQLException {
        StringBuffer sb=new StringBuffer("select * from student_mes s,grade g where s.gradeId=g.id");
        Connection connection=sqlSessionTemplate.getConfiguration().getEnvironment().getDataSource().getConnection();
        if(StringUtil.isNotEmpty(studentMes.getStuNo())){
            sb.append(" and s.stuNo like '%"+studentMes.getStuNo()+"%'");
        }
        if(StringUtil.isNotEmpty(studentMes.getStuName())){
            sb.append(" and s.stuName like '%"+studentMes.getStuName()+"%'");
        }
        if(StringUtil.isNotEmpty(studentMes.getSex())){
            sb.append(" and s.sex ='"+studentMes.getSex()+"'");
        }
        if(studentMes.getGradeId()!=-1&&studentMes.getGradeId()>0){
            sb.append(" and s.gradeId ='"+studentMes.getGradeId()+"'");
        }
        if(StringUtil.isNotEmpty(bbirthday)){
            sb.append(" and TO_DAYS(s.birthday)>=TO_DAYS('"+bbirthday+"')");
        }
        if(StringUtil.isNotEmpty(ebirthday)){
            sb.append(" and TO_DAYS(s.birthday)<=TO_DAYS('"+ebirthday+"')");
        }
        if(pageBean!=null && pageBean.getPage()>0){
            sb.append(" limit "+pageBean.getStart()+","+pageBean.getRows());
        }else {
            sb.append(" limit "+0+","+10);
        }

        PreparedStatement pstmt=connection.prepareStatement(sb.toString());
        return pstmt.executeQuery();
    }

    public Integer AddStudentMes(StudentMes studentMes) {
        return sqlSessionTemplate.getMapper(StudentMesMapper.class).AddStudentMes(studentMes);
    }

    public int UpdateStudentMes(StudentMes studentMes) {
        return sqlSessionTemplate.getMapper(StudentMesMapper.class).UpdateStudentMes(studentMes);
    }
    public int studentCount(StudentMes studentMes,String bbirthday,String ebirthday)throws Exception{
        Connection connection=sqlSessionTemplate.getConfiguration().getEnvironment().getDataSource().getConnection();
        StringBuffer sb=new StringBuffer("select count(*) as total from student_mes s,grade g where s.gradeId=g.id");
        if(StringUtil.isNotEmpty(studentMes.getStuNo())){
            sb.append(" and s.stuNo like '%"+studentMes.getStuNo()+"%'");
        }
        if(StringUtil.isNotEmpty(studentMes.getStuName())){
            sb.append(" and s.stuName like '%"+studentMes.getStuName()+"%'");
        }
        if(StringUtil.isNotEmpty(studentMes.getSex())){
            sb.append(" and s.sex ='"+studentMes.getSex()+"'");
        }
        if(studentMes.getGradeId()>0){
            sb.append(" and s.gradeId ='"+studentMes.getGradeId()+"'");
        }
        if(StringUtil.isNotEmpty(bbirthday)){
            sb.append(" and TO_DAYS(s.birthday)>=TO_DAYS('"+bbirthday+"')");
        }
        if(StringUtil.isNotEmpty(ebirthday)){
            sb.append(" and TO_DAYS(s.birthday)<=TO_DAYS('"+ebirthday+"')");
        }
        PreparedStatement pstmt=connection.prepareStatement(sb.toString());
        ResultSet rs=pstmt.executeQuery();
        if(rs.next()){
            return rs.getInt("total");
        }else{
            return 0;
        }

    }
    public int DeleteStudentMes(String delStuIds) throws SQLException {
        StringBuffer sql=new StringBuffer("delete from student_mes where stuId in("+delStuIds+")");
        Connection connection=sqlSessionTemplate.getConfiguration().getEnvironment().getDataSource().getConnection();
        PreparedStatement psmt=connection.prepareStatement(sql.toString());
        return psmt.executeUpdate();
    }

    public void excel() {
        sqlSessionTemplate.getMapper(StudentMesMapper.class).excel();
    }
}
